if object_id('BIMonitor.SSISConfigurationSettings') is not null
  drop view BIMonitor.SSISConfigurationSettings
go


/*
  future dev idea: consider changing this into a stored procedure having an @environment parameter.  The sproc 
    would return the SSIS configurations for the environment requested.  The SQL could be dynamic, and have
    a FROM clause reference to a synonym that points to the SSISConfigurations for the environment of interest.
*/


create view BIMonitor.SSISConfigurationSettings as


select
  ConfigurationFilter                            as ConfigurationGroup,
  case
    when
      PackagePath like '%OLE_SQL%' or 
      PackagePath like '%FLT_FIL%' or
      PackagePath like '%.dtsx%' or
      PackagePath like '%OLE_XLS%' or
     (PackagePath like '\Package.Connections%' and PackagePath like '%SMTP%') then
        right(left(PackagePath,len(PackagePath)-len('].Properties[ConnectionString]')),
          len(left(PackagePath,len(PackagePath)-len('].Properties[ConnectionString]')))-
          len('\Package.Connections['))
    when PackagePath like '%FileSpec%' then
        right(left(PackagePath,len(PackagePath)-len('.Properties[FileSpec]')),
          len(left(PackagePath,len(PackagePath)-len('.Properties[FileSpec]')))-
          len('\Package\'))
    when PackagePath like '\Package.Variables%' then
        right(left(PackagePath,len(PackagePath)-len('].Properties[Value]')),
          len(left(PackagePath,len(PackagePath)-len('].Properties[Value]')))-
          len('\Package.Variables[User::'))
    else 'Other'
  end                                            as SSISPropertyConfigured,
  case
    when PackagePath like '%OLE_SQL%' then 'Database Connection'
    when PackagePath like '%FLT_FIL%' then 'Flat File Connection'
    when PackagePath like '%.dtsx%' then 'SSIS Package Connection'
    when PackagePath like '%OLE_XLS]%' then 'Excel 2003 Connection'
    when PackagePath like '%OLE_XLSX%' then 'Excel 2007 Connection'
    when PackagePath like '\Package.Variables%' then 'User Variable'
    when PackagePath like '%FileSpec%' then 'File Type Specification'
    when PackagePath like '\Package.Connections%' and PackagePath like '%SMTP%' then 'SMTP Server Connection'
    else 'Other'
  end                                            as SSISPropertyType,
  case
    when ConfiguredValueType = 'String' then 'Text'
    when ConfiguredValueType = 'Int32' then 'Whole Number'
    else 'Other'
  end                                            as ConfiguredValueType,
  ConfiguredValue
from
  BIMonitor.SSISConfigurations